package han.util;

import org.apache.poi.hssf.model.InternalWorkbook;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;

import java.io.BufferedOutputStream;
import java.io.FileOutputStream;
import java.util.*;

/**
 * @fileName: ExcelUtils
 * @author: Han
 * @date: 14:44 2018/3/7
 * @dscription: 按照给定数据的格式格式生成excel
 */
public class ExcelUtils {

    public static void writeToLocal(String filePath, Map<String, List<Map<String, String>>> sheetMap) {

        Workbook workbook = ExcelUtils.generateWorkBook(sheetMap);
        BufferedOutputStream bufferedOutputStream = null;
        try {
            bufferedOutputStream = new BufferedOutputStream(new FileOutputStream(filePath));
            bufferedOutputStream.flush();
            workbook.write(bufferedOutputStream);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                Objects.requireNonNull(bufferedOutputStream).close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 生成excel工作簿
     *
     * @param sheetMap 待生成数据 key:sheet名,value:sheet里的内容
     * @return 工作簿
     */
    public static Workbook generateWorkBook(Map<String, List<Map<String, String>>> sheetMap) {

        //创建工作簿
        Workbook book = HSSFWorkbook.create(InternalWorkbook.createWorkbook());

        //样式设置
        CellStyle titleStyle = book.createCellStyle();
        titleStyle.setFillForegroundColor(HSSFColor.WHITE.index);
        titleStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
        titleStyle.setBorderBottom(CellStyle.BORDER_THIN);
        titleStyle.setBorderLeft(CellStyle.BORDER_THIN);
        titleStyle.setBorderRight(CellStyle.BORDER_THIN);
        titleStyle.setBorderTop(CellStyle.BORDER_THIN);
        titleStyle.setAlignment(CellStyle.ALIGN_CENTER);
        titleStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        // 生成一个字体
        Font font = book.createFont();
        font.setColor(HSSFColor.DARK_BLUE.index);
        font.setFontHeightInPoints((short) 12);
        font.setFontName("宋体");
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        // 把字体应用到当前的样式
        titleStyle.setFont(font);

        CellStyle contentStyle = book.createCellStyle();
        contentStyle.setFillForegroundColor(HSSFColor.WHITE.index);
        contentStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
        //设置上下左右边框
        contentStyle.setBorderBottom(CellStyle.BORDER_THIN);
        contentStyle.setBorderLeft(CellStyle.BORDER_THIN);
        contentStyle.setBorderRight(CellStyle.BORDER_THIN);
        contentStyle.setBorderTop(CellStyle.BORDER_THIN);
        contentStyle.setAlignment(CellStyle.ALIGN_CENTER);
        contentStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

        Set<String> sheetNameSet = sheetMap.keySet();
        for (String sheetName : sheetNameSet) {

            try {
                //创建sheet
                Sheet weekRiskSheet = book.createSheet(sheetName);

                //取出sheet内容
                List<Map<String, String>> contentList = sheetMap.get(sheetName);
                //生成sheet内容
                generateSheet(contentList, weekRiskSheet, titleStyle, contentStyle);
            }catch (Exception e){
                e.printStackTrace();
            }
        }

        return book;
    }

    private static void generateSheet(List<Map<String, String>> list, Sheet sheet, CellStyle titleStyle, CellStyle contentStyle)
            throws Exception {

        Set<String> colSet = list.get(0).keySet();
        int colSize = colSet.size();
        int[] colWidths = new int[colSize];

        try {

            boolean hasFormat = true;
            //填充表格内容
            for (int i = 0; i < list.size(); i++) {
                //index：第几行
                Row row2 = sheet.createRow(i + 1);
                Map<String, String> map = list.get(i);
                Set<Map.Entry<String, String>> set2 = map.entrySet();
                List<Map.Entry<String, String>> ll = new ArrayList<>(set2);
                for (int j = 0; j < ll.size(); j++) {

                    //第几列：从0开始
                    Cell cell = row2.createCell(j);
                    try {
                        String val = ll.get(j).getValue().toString();
                        //如果序号为空,不显示记录
                        if (j == 0 && val.isEmpty()) {
                            hasFormat = false;
                        }
                        //取出最大宽度
                        int width = val.getBytes().length * 250;
                        if (width > colWidths[j] && width < 25500) {
                            colWidths[j] = width;
                        } else if (width >= 25500) {
                            colWidths[j] = 25500;
                        }
                        cell.setCellValue(val);
                    } catch (Exception ignore) {
                    }
                    if (hasFormat) {
                        cell.setCellStyle(contentStyle);
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        try {
            //填充表头header
            Row row = sheet.createRow(0);
            //列字段
            int colI = 0;
            for (Object colName : colSet) {
                //设置列宽
                int width = colName.toString().getBytes().length * 340;
                if (width > colWidths[colI]) {
                    colWidths[colI] = width;
                }
                sheet.setColumnWidth(colI, colWidths[colI]);
                Cell cell = row.createCell(colI++);
                cell.setCellValue(colName.toString());
                cell.setCellStyle(titleStyle);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
